options symbolgen macrogen mlogic mprint msglevel=i;
options nocenter nodate nonumber ls=max ps=max;

libname ostruct 'F:\TDriveCopy\Research\Data\BHC_Bank_Relationships';
libname wrdsbk 'F:\TDriveCopy\Research\Data\WRDSbankdata\';


PROC DATASETS LIB=work KILL;  RUN;
quit;


%macro loop(vlist);
%let nwords=%sysfunc(countw(&vlist));

%do i=1 %to &nwords;

	%let old= %scan(&vlist, &i);
	%let new = RCON%substr(%scan(&vlist, &i),5,4);
	if &old=. then &old=&new ;
%end;
%mend;


** Call report data for each year-quarter are downloaded from WRDS ***;
%macro mergeDsets(year);

	data call&year;
	set 
	wrdsbk.call_qvards
	wrdsbk.call&year.03 
	wrdsbk.call&year.06
	wrdsbk.call&year.09
	wrdsbk.call&year.12;
	run;

%mend;


%macro callreqvar(year);

	%mergeDsets(&year); run;
	quit;


	data call2&year; set call&year;
	/*Note from Schnabl: Starting from 20110331, banks without foreign offices do not report RCFD series.
	This process replaces the missing RCFD values with RCON values*/
	%let list=RCFD2170 RCFD0010 RCFD0380 RCFD0390 RCFD0400 RCFD0600 RCFD0900 RCFD0950 RCFD1000 RCFD1350 RCFD1400 RCFD1410 RCFD1590 RCFD1600 
				RCFD1754 RCFD1766 RCFD1771 RCFD1772 RCFD1773 RCFD1935 RCFD1975 RCFD2122 RCFD2146 RCFD2165  RCFD2800 RCFD2850 
				RCFD2861 RCFD2869 RCFD2930 RCFD2948 RCFD2950 RCFD3123 RCFD3190 RCFD3200 RCFD3210 RCFD3353 RCFD3355 RCFD3365 RCFD3368 
				RCFD3381 RCFD3382 RCFD3383 RCFD3384 RCFD3401 RCFD3484 RCFD3545 RCFD3548 RCFDA247 RCFDA248 RCFDA549 RCFDA550 RCFDA551 
				RCFDA552 RCFDA553 RCFDA554 RCFDA555 RCFDA556 RCFDA557 RCFDA558 RCFDA559 RCFDA560 RCFDA561 RCFDA562 RCFDA570 RCFDA571 
				RCFDA572 RCFDA573 RCFDA574 RCFDA575 RCFDB558 RCFDB559 RCFDB560 RCFDB987 RCFDB989 RCFDB993 RCFDB995 RCFDF158 RCFDF159 
				RCFD1420 RCFD1797 RCFD5367 RCFD5368 RCFDB538 RCFDB539 RCFD2011 RCFD0071 RCFD0081 RCFD8725 RCFD8729 RCFDA589 RCFD3450 
				RCFDA126 RCFD3546 RCFDF624 RCFD2651 RCFDB571 RCFD3190 RCFD2651 RCFDB571 RCFDF060 RCFDF055 RCFD3190 RCFDF060 RCFDF055
				RCFD0213 RCFD1287 RCFD1290 RCFD1293 RCFD1295 RCFD1298 RCFD8497 RCFD8499 RCFD3123 RCFD3128 RCFD8274 RCFD8275 RCFD3792 
				RCFDA222 RCFDA223 RCFDA224 RCFD0010 RCFD1407 RCFD1403 RCFD3815 RCFDB707 RCFDB538;

	%loop(&list);

	run;


data call&year; set call2&year;

BANK_ID 			= RSSD9001; * For permco rssd_id match ****;
DATE				= RSSD9999;
YEAR				=INT(DATE/10000);
MONTH				=INT(DATE/100)-YEAR*100; 
CERT				=RSSD9050;  * FDIC CERTIFICATE ID;
REG_HIGH_HOLD		=RSSD9348;
LOCATION			=RSSD9210;    * TWO DIGITS CODE ;
ZIPCODE				=RSSD9220; 
STATE               =RSSD9200;  * TWO LETTERS ABBREVIATIONS;
INSURED             =RSSD9424;    * INSURED BANK INDICATOR RSSD9424=1 (FDIC/BIF), 2 (FDIC/SAIF) OR 6 (FDIC/BIF & FDIC/SAIF);
CHARTER    			=RSSD9048;    * =200 CHARTERED AS COMMERCIAL BANKS;

/*ASSETS*/
TOT_ASSETS		= RCFD2170; **Checked**;

/*CONSOLIDATED (FOREIGN AND DOMESTIC) LOANS. Note: For banks with no foreign branches rcfd=rcon*/
IF DATE LE 19831231 THEN TOT_LOANS_GROSS	= SUM(RCFD1400,RCFD2165); **Checked***; 
IF DATE GE 19840331 THEN TOT_LOANS_GROSS	= RCFD1400;


/* Total deposits, rcon2200, is defined excluding foreign deposits, rcfn2200. */
deposits=rcon2200; /* 197603- */
foreigndep=rcfn2200; /* 197603- */

demanddep=rcon2210; /* 197603- Demand deposits*/
transdep=rcon2215; /* 198403- Transaction deposits*/
brokereddep=rcon2365; /* 198309- Brokered deposits*/
brokereddeplt100k=rcon2343; /*19840331-20161231- Brokered deposits issued by the bank in denominations of less than $100,000 */
brokereddepge100k=rcon2344; /*19840331-20091231- Brokered deposits issued by the bank in denominations equal to or greater than $100,000 */
timedepge100k=rcon2604; /* 197603- Total time deposits of $100K or more*/
if timedepge100k=. then timedepge100k = sum(rconj473, rconj474); /* 197603- Sum of time deposits $100K-$250K and time deposits of $250K or more*/
timedeplt100k=rcon6648; /* 198403- Total time deposits less than $100K*/
cdge100k=rcon6645; /*197603-199703 Time certificates of deposit of $100K or more in domestic offices*/
timedepgt250k=rconj474; /* 201003- Total time deposits more than $250K*/
timedeple250k=rconj473+rcon6648; /* 201003- Total time deposits of $250K or less. Sum of time deposits $100K-$250K (j473) and time deposits less than $100K (6648)*/
		

brokereddeplt100k_lt1yr=rconA243; /*BROKERED DEPOSITS ISSUED IN DENOMINATIONS OF LESS THAN $100,000 WITH A REMAINING MATURITY OF ONE YEAR OR LESS (1996-03-31 to 2016-12-31)*/
brokereddepge100k_lt1yr=rconA244; /*BROKERED DEPOSITS ISSUED IN DENOMINATIONS OF $100,000 OR MORE WITH A REMAINING MATURITY OF ONE YEAR OR LESS (1996-03-31 to 2016-12-31)*/

timedepge100k_lt1yr=rconA242; /*TIME DEPOSITS OF $100,000 OR MORE WITH A REMAINING MATURITY OF ONE YEAR OR LESS (1996-03-31 to 2016-12-31)*/
foreigntimedep_lt1yr=rcfnA245; /*TIME DEPOSITS IN FOREIGN OFFICES WITH A REMAINING MATURITY OF ONE YEAR OR LESS (1996-03-31 to ---)*/

fedfundspur=RCONB993; /*FEDERAL FUNDS PURCHASED IN DOMESTIC OFFICES (2002-03-31 -- )*/
repos=RCFDB995; /*SECURITIES SOLD UNDER AGREEMENTS TO REPURCHASE (2002-03-31 -- )*/
tradingliab_exclreval=sum(RCFD3548,-RCFD3547); /*(1996-03-31 -- )*/

/* Fed funds purchased and securities sold under agreements to repurchase is rcfd2800 through 20011231, after which it is broken up into Fed funds purchased,
		   rcfdB993 and securities sold under agreements to repurchase, rcfdB995. Note that the balance sheet includes only Fed funds ourchased in domestic offices,
		   rconB993. However, using this series would not form a consistent time series with rcfd2800. */
fedfundsrepoliab=rcfd2800; /* 197603-200112 */
if fedfundsrepoliab=. then fedfundsrepoliab=sum(rcfdB993,rcfdB995); /* 200203- */

	/* Trading liabilities, rcfd3548, starts in 19940331. There does not appear to be a corresponding series prior to that date. */
		tradingliabilities=rcfd3548; /* 199403- */

	/* Other borrowed money is reported as rcfd2850 until 19931231 then rcfd3190 after that. It can also be reconstructed as the sum of rcfd1935, total other
		   borrowed money owed to nonrelated banks in foreign countries, rcfd2861, other borrowed money owed to nonrelated banks in the U.S. (including their IBFs),
		   and rcfd2869, total other borrowed money owed to others. */
		otherborrowedmoney=rcfd3190; /* 199403- */
		if otherborrowedmoney=. then otherborrowedmoney=rcfd2850; /* 197612-199312 */
		if otherborrowedmoney=. then otherborrowedmoney=sum(rcfd1935,rcfd2861,rcfd2869); /* 198006- */

		IF DATE<20060930 THEN DO;
		otherborrowedmoney_lt1yr = SUM(RCFD2651, RCFDB571); *Reported after 03/31/2001;
		otherborrowedmoney_gt1yr = SUM(RCFD3190, -RCFD2651, -RCFDB571); *Reported after 03/31/2001;
		END;
		ELSE IF DATE>=20060930 THEN DO;
		otherborrowedmoney_lt1yr = SUM(RCFDF060, RCFDF055); *Reported after 09/30/2006;
		otherborrowedmoney_gt1yr = SUM(RCFD3190, -RCFDF060, -RCFDF055); *Reported after 09/30/2006;
		END;


TOT_LIABILITIES	= RCFD2948;
		/** Didnt find commercial paper item in call reports. Checked Discover call report. **/

 
		/*REGULATORY CAPITAL - AFTER MARCH 1994*/
TIER1_CAP			= RCFD8274;
TIER2_CAP			= RCFD8275;

TOT_RISK_BASED_CAP	= RCFD3792;	*Reported after 03/31/1996;

EXCESS_ALLOW_LOAN_LOSS  = RCFDA222;
NET_RISK_WT_ASSETS	    = RCFDA223;
AVERAGE_TOTAL_ASSETS    = RCFDA224;


  CHARGEOFFS				= RIAD4635;
  PROVISION_LOANS_LOSSES	= RIAD4230;
  ALLOC_TRANS_RISK_RESERVES = RCFD3128;
  ALW_LOAN_LEASES_LOSSES	= RCFD3123;


NET_INCOME 			= RIAD4340;  
EQUITY_CAPITAL		= RCFD3210;

CASH				= RCFD0010;
GOVSEC				= sum(RCFD0213,RCFD1287,RCFD1290,RCFD1293,RCFD1295,RCFD1298,RCFD8497,RCFD8499);


NONPERF_LOANS=sum(RCFD1407,RCFD1403);

UNUSED_CC_COMMIT=RCFD3815; ** UNUSED COMMITMENTS - CREDIT CARD LINES; 
CC_SOLD_SEC=RCFDB707; ** OUTSTANDING PRINCIPAL BALANCE OF CREDIT CARDS SOLD AND SECURITIZED ***;	
TOT_CC_LOANS=RCFDB538; ** CREDIT CARDS LOANS FROM SCHEDULE HC-C;
MORTGAGE_LOANS = RCFD1410;


drop RCON: RSSD: RCF: RIAD: TEXT: CALL: TE0: TE1: EDGC: RCRI: XMBA: DATE_SAS ENTITY RC0: RC1: RCX: ID_LEI;
RUN;

proc datasets;
append base=calldata0217 data=call&year force;
run;

proc datasets library=work;
delete call&year call2&year;
run;


%mend;



%macro runAllYrs;
%let yearlist=01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17; 


%let k=1;

%do %while (%scan(&yearlist,&k) ne );

	%let year = %scan(&yearlist,&k);

	%callreqvar(&year); run;
	
	%let k = %eval(&k+1);

%end;
%mend runAllYrs;


proc delete data=calldata0217;
run;

%runAllYrs; run;
quit;



*** Get bank relationship data from https://www.ffiec.gov/npw/FinancialReport/DataDownload and unzip to get the "relationships" dataset ***;
*** USE THE FOLLOWING TO IMPORT CSV Files AND SAVE AS SAS DATASETS ***;

/*%let file=TRANSFORMATIONS;*/
/*%let textfile='F:\TDriveCopy\Data\BHC_Bank_Relationships\CSV_&file..CSV';*/
/*%let sasfile = &file._04062022;*/
/**/
/**/
/*data _null_;*/
/*infile "&textfile";*/
/*input ;*/
/*call symput("nvar",_n_);*/
/*run;*/
/*%put &nvar;*/
/**/
/*PROC IMPORT OUT= work.DataFile */
/*            DATAFILE= "&textfile"*/
/*            DBMS=dlm REPLACE;*/
/*     DELIMITER=',';*/
/*	 GETNAMES=YES;*/
/*	 GUESSINGROWS=&nvar;*/
/*RUN;*/
/*quit;*/
/**/
/**/
/*data ostruct.&sasfile; set DataFile;*/
/*run;*/


data relationships; set ostruct.relationships_04062022;
if (missing(dt_end)=0 & dt_end ne 0) then date_end = input(strip(dt_end),yymmdd8.);
format date_end yymmddN8.;
if (missing(dt_start)=0 & dt_start ne 0) then date_start = input(strip(dt_start),yymmdd8.);
format date_start yymmddN8.;
drop dt_end dt_start;
proc sort nodupkey; by ID_RSSD_PARENT ID_RSSD_OFFSPRING date_start reln_lvl; **** 0 obs deleted ****;
run;


proc sql noprint noprint;
create table Subs as
select ID_RSSD_OFFSPRING, RELN_LVL, CTRL_IND, REG_IND from relationships;
quit;


proc sql noprint noprint;
create table FinalDset_Call as
select a.*, b.* from Subs as a left join calldata0217 as b
on a.ID_RSSD_OFFSPRING = b.BANK_ID;
quit;

proc sort data=FinalDset_Call; by rank ultimateRSSD_ID BANK_ID DATE;
run;

proc export data=FinalDset_Call
outfile="F:\TDriveCopy\Research\Data\CallDataForBanks.dta" replace;
run;
